--Thông tin nhóm
--1241336 - Nguyễn Trung Hậu
--1241393 - Đàm Thiệu Quang
--1241444 - Nguyễn Thị Yến

---------dirty read---------
--Kịch bản:
--Thủ thư cập nhật tình trạng cho một đầu (DauSach) sách và xảy ra lỗi, vô tình đọc giả muốn xem đầu sách đó có còn không để mượn. Dẫn đến sách còn mà đọc giả thấy là hết nên không thực hiện được việc mượn.


--Xem thong tin dau sach
ALTER PROC sp_xemDauSach @isbn int
AS
Begin TRAN
	--set tran isolation level read uncommitted
	WAITFOR DELAY '00:00:05'
	SELECT * 
	FROM DauSach ds 
	WHERE ds.isbn=@isbn
COMMIT TRAN	


--4.6 -- cập nhật trạng thái của đầu sách
ALTER PROCEDURE sp_CapnhatTrangthaiDausach @isbn int
AS
BEGIN TRAN
	IF ((SELECT
			COUNT(ma_cuonsach)
		FROM cuonsach
		WHERE isbn = @isbn)
		- (SELECT
			COUNT(ma_cuonsach)
		FROM cuonsach
		WHERE tinhtrang = 'N' AND isbn=@isbn)
		= 0)
	BEGIN
		UPDATE dausach
		SET trangthai = 'N'
		WHERE isbn = @isbn
		WAITFOR DELAY '00:00:10'
	END
	ELSE
	BEGIN
		UPDATE dausach
		SET trangthai = 'Y'
		WHERE isbn = @isbn
		WAITFOR DELAY '00:00:10'
	END
	
ROLLBACK TRAN
GO


--Tran 1
SELECT * FROM CuonSach cs where isbn=1
EXEC sp_CapnhatTrangthaiDausach 1
SELECT * FROM DauSach ds where isbn=1


--Tran 2

EXEC sp_xemDauSach 1


---------phantom---------

--Kịch bản:
--người muốn xem số lượng đầu sách của tác giả nào đó trong thư viện, trong quá trình xem thì thủ thư lại cập nhật thêm một đầu sách mới cũng của tác giả đó, dẫn để tổng số đầu sách bị thay đổi.


--4.7 -- Thêm tựa sách mới
create PROCEDURE sp_ThemTuaSach @TuaSach nvarchar(63), @TacGia nvarchar(31), @TomTat nvarchar(222)
AS
BEGIN
	DECLARE @MA int
	SET @MA = 1
	WHILE @MA IN (SELECT
			ma_tuasach
		FROM tuasach)
	BEGIN
		SET @MA = @MA + 1
	END
	IF (@TuaSach IS NULL OR @TacGia IS NULL OR @TomTat IS NULL)
	BEGIN
		PRINT N'Tựa sách nhập không đúng quy định'
	END
	ELSE
	BEGIN
		
		INSERT INTO tuasach
			VALUES (@MA, @TuaSach, @TacGia, @TomTat)
	END
	WAITFOR DELAY '00:00:01'
END
GO


--Xem tác giả
alter PROC sp_xemTacGia @tenTacGia nvarchar(31)
AS
BEGIN tran
	set tran isolation level serializable
	DECLARE @tongsp int, @matuasach int
	BEGIN TRY                
                DECLARE cursor_name CURSOR DYNAMIC FOR
                SELECT ts.ma_tuasach FROM TuaSach ts WHERE ts.tacgia=@tenTacGia
                
                OPEN cursor_name;
				SET @tongsp = (SELECT COUNT(*) FROM TuaSach ts WHERE ts.tacgia=@tenTacGia )				
	
				PRINT N'Tổng số sách:' + CAST(@tongsp AS char(3))
				PRINT N'_________________________________________'

                FETCH NEXT FROM cursor_name INTO @matuasach;
                
                WHILE @@FETCH_STATUS = 0
                BEGIN              
					PRINT @matuasach
					WAITFOR DELAY '00:00:01'
                FETCH NEXT FROM cursor_name INTO @matuasach;
                
                END
                
                CLOSE cursor_name;
                DEALLOCATE cursor_name;      
    END TRY
	BEGIN CATCH
		declare @ErrorMsg varchar(2000)
		select @ErrorMsg = N'Lỗi: ' + ERROR_MESSAGE()
		raiserror(@ErrorMsg, 16,1)
		rollback tran
		return
	END CATCH
COMMIT TRAN


--Tran 1
EXEC sp_xemTacGia 'Pearl S. Buck'

--Tran 2
--DELETE FROM TuaSach WHERE ma_tuasach = 51

SELECT * FROM TuaSach ts WHERE ts.tacgia='Pearl S. Buck'
EXEC sp_ThemTuaSach	N'Anh văn 3','Pearl S. Buck','b'
SELECT * FROM TuaSach ts WHERE ts.tacgia='Pearl S. Buck'



-------Unrepeatable read--------
--kịch bản:
--Độc giả A đang xem thông tin Đầu sách isbn=1 còn DUY NHẤT 1 cuốn sách có ma_cuonsach=3, TinhTrang=Y nên đầu sách có TrangThai=Y
--thì 1 độc giả B mượn cuốn sách isbn=1
--Kết quả: tất cả cuốn sách của đầu sách isbn=1 sẽ có TinhTrang=N
--và đầu sách isbn=1 được cập nhật TrangThai=N 

--4.2--Xem thong tin dau sach
ALTER PROCEDURE sp_ThongtinDausach @isbn int
AS
BEGIN TRAN
	
	--set tran isolation level repeatable read
	SELECT
		ts.TuaSach,
		ts.tacgia,
		ds.ngonngu,
		ds.bia,
		ds.trangthai,
		ts.tomtat,
		COUNT(*) AS SachChuaMuon
	FROM	DauSach ds,
			TuaSach ts,
			CuonSach cs
	WHERE ds.ma_tuasach = ts.ma_tuasach 
	AND ds.isbn = cs.isbn AND ds.isbn = @isbn
	GROUP BY	ds.isbn,
				ts.TuaSach,
				ts.tacgia,
				ds.ngonngu,
				ds.bia,
				ds.trangthai,
				ts.tomtat
WAITFOR DELAY '00:00:05'
COMMIT TRAN
GO

--4.12--mượn sách
ALTER PROC sp_MuonSach @madocgia smallint, @isbn int
AS
BEGIN TRAN
	BEGIN TRY
		IF EXISTS (SELECT
				*
			FROM Muon
			WHERE isbn = @isbn AND ma_docgia = @madocgia)
		BEGIN
			PRINT 'Doc gia dang muon sach cung loai'
			RETURN
		END
		ELSE
		BEGIN
			--Kiem tra so sach doc gia dang muon:

			DECLARE	@sachdangmuon int,
					@docgiamuon int,
					@flag int
			SET @sachdangmuon = 0
			SET @flag = 0
			SET @docgiamuon = 0

			SELECT
				@docgiamuon = COUNT(*)
			FROM Muon m
			WHERE m.ma_docgia = @madocgia 
				AND m.ngay_hethan > GETDATE()
			PRINT @docgiamuon

			--Kiem tra docgia la nguoilon
			IF @madocgia IN (SELECT
					ma_docgia
				FROM NguoiLon)
			BEGIN
				SELECT
					@flag = COUNT(*)
				FROM	Muon m,
						TreEm te
				WHERE m.ma_docgia = te.ma_docgia 
				AND m.ngay_hethan > GETDATE()
				AND te.ma_docgia_nguoilon = @madocgia

				SET @sachdangmuon = @docgiamuon + @flag
				PRINT @sachdangmuon

				IF (@sachdangmuon > 5)
				BEGIN
					PRINT N'Độc giả đã mượn tối đa 5 quyển.'
					RETURN
				END
			END

			--Kiem tra docgia la treem
			ELSE
			IF @madocgia IN (SELECT
					ma_docgia
				FROM TreEm)
			BEGIN
				SET @sachdangmuon = 0
				SET @flag = 0
				PRINT @docgiamuon
				IF (@docgiamuon = 1)
				BEGIN
					PRINT N'Độc giả đã mượn tối đa 1 quyển.'
					RETURN
				END
				ELSE
				IF (@docgiamuon >= 0 AND @docgiamuon < 1)
				BEGIN
					SELECT
						@flag = COUNT(*)
					FROM	Muon m,
							TreEm te
					WHERE m.ma_docgia = te.ma_docgia_nguoilon 
					AND m.ngay_hethan > GETDATE()
					AND te.ma_docgia = @madocgia

					SET @sachdangmuon = @docgiamuon + @flag
					PRINT @sachdangmuon
					IF (@sachdangmuon = 5)
					BEGIN
					 PRINT N'Người bảo lãnh độc giả đã mượn tối đa 5 quyển.'
						RETURN
					END
				END
			END
			--Kiem tra con sach trong thu vien khong?
			DECLARE @cur CURSOR
			SET @cur = CURSOR FOR (SELECT
				Ma_CuonSach
			FROM CuonSach cs
			WHERE cs.isbn = @isbn AND cs.TinhTrang = 'Y')

			OPEN @cur
			FETCH NEXT FROM @cur INTO @flag
			IF @@fetch_status = 0
			BEGIN
				--insert bang Muon
	
				INSERT INTO Muon(isbn,ma_cuonsach,ma_docgia,
									ngayGio_muon,ngay_hethan)					
				VALUES(1,3,105,GETDATE(),DATEADD(dd, 14,GETDATE()))

				--Cap nhat tinh trang Cuonsach
				UPDATE CuonSach
				SET TinhTrang = 'N'
				WHERE Ma_CuonSach = @flag

				--Cap nhat trang thai Dausach
				EXEC sp_CapNhatTrangThaiDauSach @isbn
				PRINT N'Mượn sách thành công.'
			END
			ELSE
			BEGIN
				PRINT N'Khong còn cuốn sách độc giả cần mượn.
						Độc giả sẽ được đăng kí mượn.'
			INSERT INTO DangKy(isbn,ma_docgia,ngaygio_dk,ghichu)
					VALUES (@isbn, @madocgia, GETDATE(), NULL)
			END
			CLOSE @cur
			DEALLOCATE @cur
		END
	END TRY
	BEGIN CATCH
		RAISERROR (N'Lỗi',16,1)
		ROLLBACK TRAN
		RETURN
	END CATCH
COMMIT TRAN
GO

--Store phụ
ALTER PROCEDURE sp_CapnhatTrangthaiDausach @isbn int
AS
BEGIN TRAN
	IF ((SELECT
			COUNT(ma_cuonsach)
		FROM cuonsach
		WHERE isbn = @isbn)
		- (SELECT
			COUNT(ma_cuonsach)
		FROM cuonsach
		WHERE tinhtrang = 'N' AND isbn=@isbn)
		= 0)
	BEGIN
		UPDATE dausach
		SET trangthai = 'N'
		WHERE isbn = @isbn
		--WAITFOR DELAY '00:00:05'
	END
	ELSE
	BEGIN
		UPDATE dausach
		SET trangthai = 'Y'
		WHERE isbn = @isbn
		--WAITFOR DELAY '00:00:05'
	END
	
COMMIT TRAN
GO

--tran 1
EXEC sp_ThongtinDausach '1'
select * from DauSach where isbn=1

--tran 2
--vì bảng Muon đã có record có "isbn=1 and ma_cuonsach=3" 
--nên cô nên xóa trước khi chạy demo
--delete from Muon where isbn=1 and ma_cuonsach=3

exec sp_MuonSach 105,1
--select * from DauSach where isbn=1